I'm close on this but missing something. How do I only get the first and last links in chains such as A->B, B->C? How do I just get A->C?
CREATE TEMP TABLE IF NOT EXISTS chains (
cname TEXT PRIMARY KEY,
becomes TEXT
);
INSERT INTO chains
VALUES
('A', NULL),
('B', 'C'),
('C', 'D'),
('D', 'E'),
('E', NULL)
;
WITH RECURSIVE
final_link AS (
SELECT
chains.cname,
chains.becomes
FROM
chains
UNION
SELECT
chains.cname,
final_link.becomes
FROM
chains
INNER JOIN final_link
ON chains.becomes = final_link.cname
)
SELECT * FROM final_link;
The results I would like are:
cname | becomes
------|--------
'B' | 'E'
'C' | 'E'
'D' | 'E'
Here is one approach:
with recursive final_link as (
select cname, becomes, cname original_cname, 0 lvl
from chains
where becomes is not null
union all
select c.cname, c.becomes , f.original_cname, f.lvl + 1
from chains c
inner join final_link f on f.becomes = c.cname
where c.becomes is not null
)
select distinct on (original_cname) original_cname, becomes
from final_link
order by original_cname, lvl desc
The idea is to have the subquery keep track of the starting node, and of the level of each node in the tree. You can then filter with distinct on in the outer query.
original_cname | becomes :------------- | :------ B | E C | E D | E
You can achieve this by starting the recursion only with the chain ends, not with all links, then iteratively prepending links as you are already doing:
WITH RECURSIVE final_link AS (
SELECT cname, becomes
FROM chains c
WHERE (SELECT becomes IS NULL FROM chains WHERE cname = c.becomes)
UNION
SELECT c.cname, fl.becomes
FROM chains c
INNER JOIN final_link fl ON c.becomes = fl.cname
)
SELECT * FROM final_link;
(Demo)